﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE
Public Class PestanaInformeMensualMonitoreoDA

    Public Function Informe_Grilla_Fisico(ByVal strPOA As String, ByVal strPeriodo As String, ByVal strVersionPoaInforme As String) As DataTable

        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objdt As DataTable
        Dim lxc_SQL As String
        Dim strCadAuxiEspecial As String

        If strPeriodo = "201401" Then
            strCadAuxiEspecial = " ( NVL(PAFS.META_01,0) + NVL(PAFS.META_02,0) + NVL(PAFS.META_03,0) + NVL(PAFS.META_04,0) + NVL(PAFS.META_05,0) + NVL(PAFS.META_06,0) + NVL(PAFS.META_07,0) + NVL(PAFS.META_08,0) + NVL(PAFS.META_09,0) + NVL(PAFS.META_10,0) + NVL(PAFS.META_11,0) + NVL(PAFS.META_12,0) ) "
        Else
            strCadAuxiEspecial = "  (CASE WHEN TO_NUMBER(PDO.MES) >= 1 THEN NVL(PAFS.META_01,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 2 THEN NVL(PAFS.META_02,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 3 THEN NVL(PAFS.META_03,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 4 THEN NVL(PAFS.META_04,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 5 THEN NVL(PAFS.META_05,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 6 THEN NVL(PAFS.META_06,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 7 THEN NVL(PAFS.META_07,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 8 THEN NVL(PAFS.META_08,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 9 THEN NVL(PAFS.META_09,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 10 THEN NVL(PAFS.META_10,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 11 THEN NVL(PAFS.META_11,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 12 THEN NVL(PAFS.META_12,0) ELSE 0 END " + _
                                   "  )  "
        End If

        Try

            lxc_SQL = "SELECT DISTINCT PAFS.CODIGO, PAFS.TAREA_ACTIVIDAD_DESCRIPCION,PAFS.UNIDAD,UNI.DESCRIPCION ,PAFS.META_ANUAL,  " & _
                     "CASE PDO.MES " & _
                     "WHEN '01' THEN  PAFS.META_01 " & _
                     "WHEN '02' THEN  PAFS.META_02 " & _
                     "WHEN '03' THEN  PAFS.META_03 " & _
                     "WHEN '04' THEN  PAFS.META_04 " & _
                     "WHEN '05' THEN  PAFS.META_05 " & _
                     "WHEN '06' THEN  PAFS.META_06 " & _
                     "WHEN '07' THEN  PAFS.META_07 " & _
                     "WHEN '08' THEN  PAFS.META_08 " & _
                     "WHEN '09' THEN  PAFS.META_09 " & _
                     "WHEN '10' THEN  PAFS.META_10 " & _
                     "WHEN '11' THEN  PAFS.META_11 " & _
                     "WHEN '12' THEN  PAFS.META_12 " & _
                     " END AS PROGRAMADO_MES, " & _
                     "(  " & _
                      "(CASE WHEN FECHA_VISITA4 IS NOT NULL THEN NVL2(PFP.META_MENSUAL4, PFP.META_MENSUAL4, 0)  ELSE " & _
                      "(CASE WHEN FECHA_VISITA3 IS NOT NULL THEN NVL2(PFP.META_MENSUAL3, PFP.META_MENSUAL3, 0)  ELSE  " & _
                      "(CASE WHEN FECHA_VISITA2 IS NOT NULL THEN NVL2(PFP.META_MENSUAL2, PFP.META_MENSUAL2, 0)  ELSE  " & _
                      "NVL2(PFP.META_MENSUAL1, PFP.META_MENSUAL1, 0)  END)END)END) " & _
                      "-  " & _
                     "EJEC_EN_EL_MES  " & _
                     ") AS EJECUTADO1,  " & _
                     "DIV_POR_CERO (  " & _
                     "(  " & _
                      "(CASE WHEN FECHA_VISITA4 IS NOT NULL THEN NVL2(PFP.META_MENSUAL4, PFP.META_MENSUAL4, 0)  ELSE " & _
                      "(CASE WHEN FECHA_VISITA3 IS NOT NULL THEN NVL2(PFP.META_MENSUAL3, PFP.META_MENSUAL3, 0)  ELSE  " & _
                      "(CASE WHEN FECHA_VISITA2 IS NOT NULL THEN NVL2(PFP.META_MENSUAL2, PFP.META_MENSUAL2, 0)  ELSE  " & _
                      "NVL2(PFP.META_MENSUAL1, PFP.META_MENSUAL1, 0)  END)END)END) " & _
                     " - " & _
                     "EJEC_EN_EL_MES " & _
                     ") " & _
                     ", " & _
                     "CASE PDO.MES " & _
                     "WHEN '01' THEN  PAFS.META_01 " & _
                     "WHEN '02' THEN  PAFS.META_02 " & _
                     "WHEN '03' THEN  PAFS.META_03 " & _
                     "WHEN '04' THEN  PAFS.META_04 " & _
                     "WHEN '05' THEN  PAFS.META_05 " & _
                     "WHEN '06' THEN  PAFS.META_06 " & _
                     "WHEN '07' THEN  PAFS.META_07 " & _
                     "WHEN '08' THEN  PAFS.META_08 " & _
                     "WHEN '09' THEN  PAFS.META_09 " & _
                     "WHEN '10' THEN  PAFS.META_10 " & _
                     "WHEN '11' THEN  PAFS.META_11 " & _
                     "WHEN '12' THEN  PAFS.META_12 " & _
                     "END " & _
                       ") AS PORCENTAJE , " & _
                      " " + strCadAuxiEspecial + " AS ACUMULADO,  " & _
                      "(CASE WHEN FECHA_VISITA4 IS NOT NULL THEN NVL2(PFP.META_MENSUAL4, PFP.META_MENSUAL4, 0)  ELSE " & _
                      "(CASE WHEN FECHA_VISITA3 IS NOT NULL THEN NVL2(PFP.META_MENSUAL3, PFP.META_MENSUAL3, 0)  ELSE  " & _
                      "(CASE WHEN FECHA_VISITA2 IS NOT NULL THEN NVL2(PFP.META_MENSUAL2, PFP.META_MENSUAL2, 0)  ELSE  " & _
                      "NVL2(PFP.META_MENSUAL1, PFP.META_MENSUAL1, 0)  END)END)END) AS EJECUTADO,  " & _
                      "ROUND(CASE WHEN  " + strCadAuxiEspecial + " = 0 THEN 0 ELSE " & _
                      "(CASE WHEN FECHA_VISITA4 IS NOT NULL THEN NVL2(PFP.META_MENSUAL4, PFP.META_MENSUAL4, 0)  ELSE " & _
                      "(CASE WHEN FECHA_VISITA3 IS NOT NULL THEN NVL2(PFP.META_MENSUAL3, PFP.META_MENSUAL3, 0)  ELSE  " & _
                      "(CASE WHEN FECHA_VISITA2 IS NOT NULL THEN NVL2(PFP.META_MENSUAL2, PFP.META_MENSUAL2, 0)  ELSE  " & _
                      "NVL2(PFP.META_MENSUAL1, PFP.META_MENSUAL1, 0)  END)END)END) / " + strCadAuxiEspecial + " END,4) PORCENTAJE2 " & _
                "FROM POA_FISICA_HISTORICO PAFS " & _
                      "LEFT JOIN POA_HISTORICO P ON P.POA=PAFS.POA " & _
                      "LEFT JOIN POA_FISICO_PERSONA PFP ON PFP.ID_POA_ACTIVIDAD_FISICA =PAFS.ID_POA_ACTIVIDAD_FISICA " & _
                      "INNER JOIN POA_FISICA_HISTORICO PAFS ON PAFS.ID_POA_ACTIVIDAD_FISICA = PFP.ID_POA_ACTIVIDAD_FISICA " & _
                      "INNER JOIN PLAN_TRABAJO_POA PTP ON PTP.ID_PLAN_TRABAJO_POA=PFP.ID_PLAN_TRABAJO_POA  " & _
                      "INNER JOIN PLAN_TRABAJO_PERSONA PTX ON PTX.ID_PLAN_TRABAJO_PERSONA=PTP.ID_PLAN_TRABAJO_PERSONA " & _
                      "INNER JOIN UNIDAD UNI ON UNI.UNIDAD=PAFS.UNIDAD  " & _
                      "INNER JOIN PERIODO PDO ON PDO.PERIODO=PTX.PERIODO  " & _
                "WHERE PAFS.POA = '" & strPOA & "' AND  " & _
                      "PDO.PERIODO = '" & strPeriodo & "' AND  " & _
                      "PFP.ESTADO='2' AND " & _
                      "P.VERSION = PAFS.VERSION AND " & _
                      "P.VERSION = PFP.VERSION AND " & _
                      "P.VERSION = '" & strVersionPoaInforme & "' " & _
                "ORDER BY " & _
                    " TO_NUMBER(NVL2(REGEXP_SUBSTR(PAFS.CODIGO,'[^._]+',1,1), REGEXP_SUBSTR(PAFS.CODIGO,'[^._]+',1,1), 0)) ASC, " + _
                    " TO_NUMBER(NVL2(REGEXP_SUBSTR(PAFS.CODIGO,'[^._]+',1,2), REGEXP_SUBSTR(PAFS.CODIGO,'[^._]+',1,2), 0)) ASC, " + _
                    " TO_NUMBER(NVL2(REGEXP_SUBSTR(PAFS.CODIGO,'[^._]+',1,3), REGEXP_SUBSTR(PAFS.CODIGO,'[^._]+',1,3), 0)) ASC, " + _
                    " TO_NUMBER(NVL2(REGEXP_SUBSTR(PAFS.CODIGO,'[^._]+',1,4), REGEXP_SUBSTR(PAFS.CODIGO,'[^._]+',1,4), 0)) ASC, " + _
                    " TO_NUMBER(NVL2(REGEXP_SUBSTR(PAFS.CODIGO,'[^._]+',1,5), REGEXP_SUBSTR(PAFS.CODIGO,'[^._]+',1,5), 0)) ASC "

            '    "INNER JOIN POA_ACTIVIDAD_FISICA PAFS ON PAFS.ID_POA_ACTIVIDAD_FISICA = PFP.ID_POA_ACTIVIDAD_FISICA " & _
            '    "INNER JOIN PLAN_TRABAJO_POA PTP ON PTP.ID_PLAN_TRABAJO_POA=PFP.ID_PLAN_TRABAJO_POA  " & _
            '    "INNER JOIN PLAN_TRABAJO_PERSONA PTX ON PTX.ID_PLAN_TRABAJO_PERSONA=PTP.ID_PLAN_TRABAJO_PERSONA " & _
            '    "INNER JOIN UNIDAD UNI ON UNI.UNIDAD=PAFS.UNIDAD  " & _
            '    "INNER JOIN PERIODO PDO ON PDO.PERIODO=PTX.PERIODO  " & _
            '"WHERE PAFS.POA= '" & strPOA & "' AND  PDO.PERIODO='" & strPeriodo & "' AND PFP.ESTADO='2'ORDER BY PAFS.CODIGO ASC "

            objdt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objdt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function Informe_Grilla_Financiero(ByVal strPOA As String, ByVal strPeriodo As String, ByVal strVersionPoaInforme As String) As DataTable

        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objdt As DataTable
        Dim lxc_SQL As String
        Dim strCadAuxiEspecial As String

        If strPeriodo = "201401" Then
            strCadAuxiEspecial = " ( NVL(PAF.META_01,0) + NVL(PAF.META_02,0) + NVL(PAF.META_03,0) + NVL(PAF.META_04,0) + NVL(PAF.META_05,0) + NVL(PAF.META_06,0) + NVL(PAF.META_07,0) + NVL(PAF.META_08,0) + NVL(PAF.META_09,0) + NVL(PAF.META_10,0) + NVL(PAF.META_11,0) + NVL(PAF.META_12,0) ) "
        Else
            strCadAuxiEspecial = "  (CASE WHEN TO_NUMBER(PDO.MES) >= 1 THEN NVL(PAF.META_01,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 2 THEN NVL(PAF.META_02,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 3 THEN NVL(PAF.META_03,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 4 THEN NVL(PAF.META_04,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 5 THEN NVL(PAF.META_05,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 6 THEN NVL(PAF.META_06,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 7 THEN NVL(PAF.META_07,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 8 THEN NVL(PAF.META_08,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 9 THEN NVL(PAF.META_09,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 10 THEN NVL(PAF.META_10,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 11 THEN NVL(PAF.META_11,0) ELSE 0 END + " + _
                                    " CASE WHEN TO_NUMBER(PDO.MES) >= 12 THEN NVL(PAF.META_12,0) ELSE 0 END " + _
                                   "  )  "
        End If

        Try

            lxc_SQL = "SELECT DISTINCT PAF.GENERICA_GASTO ,PAF.ESPECIFICA_GASTO,EG.DESCRIPCION, PAF.META_ANUAL, " & _
                      "CASE PDO.MES  " & _
                      "WHEN '01' THEN  PAF.META_01 " & _
                      "WHEN '02' THEN  PAF.META_02   " & _
                      "WHEN '03' THEN  PAF.META_03   " & _
                      "WHEN '04' THEN  PAF.META_04   " & _
                      "WHEN '05' THEN  PAF.META_05   " & _
                      "WHEN '06' THEN  PAF.META_06   " & _
                      "WHEN '07' THEN  PAF.META_07   " & _
                      "WHEN '08' THEN  PAF.META_08   " & _
                      "WHEN '09' THEN  PAF.META_09   " & _
                      "WHEN '10' THEN  PAF.META_10   " & _
                      "WHEN '11' THEN  PAF.META_11   " & _
                      "WHEN '12' THEN  PAF.META_12   " & _
                      "END AS PROGRAMADO_MES,  " & _
                      "( " & _
                      "(CASE WHEN FECHA_VISITA4 IS NOT NULL THEN NVL2(PFP.META_MENSUAL4, PFP.META_MENSUAL4, 0)  ELSE " & _
                      "(CASE WHEN FECHA_VISITA3 IS NOT NULL THEN NVL2(PFP.META_MENSUAL3, PFP.META_MENSUAL3, 0)  ELSE  " & _
                      "(CASE WHEN FECHA_VISITA2 IS NOT NULL THEN NVL2(PFP.META_MENSUAL2, PFP.META_MENSUAL2, 0)  ELSE  " & _
                      "NVL2(PFP.META_MENSUAL1, PFP.META_MENSUAL1, 0)  END)END)END) " & _
                      "- " & _
                      "EJEC_EN_EL_MES " & _
                      ") AS EJECUTADO1,  " & _
                     "DIV_POR_CERO " & _
                     "(  " & _
                     "( " & _
                     "(CASE WHEN FECHA_VISITA4 IS NOT NULL THEN NVL2(PFP.META_MENSUAL4, PFP.META_MENSUAL4, 0)  ELSE  " & _
                     "(CASE WHEN FECHA_VISITA3 IS NOT NULL THEN NVL2(PFP.META_MENSUAL3, PFP.META_MENSUAL3, 0)  ELSE  " & _
                     "(CASE WHEN FECHA_VISITA2 IS NOT NULL THEN NVL2(PFP.META_MENSUAL2, PFP.META_MENSUAL2, 0)  ELSE   " & _
                     "NVL2(PFP.META_MENSUAL1, PFP.META_MENSUAL1, 0)  END)END)END) " & _
                     "- " & _
                     "EJEC_EN_EL_MES " & _
                     ") " & _
                     ", " & _
                     "CASE PDO.MES " & _
                     "WHEN '01' THEN  PAF.META_01 " & _
                     "WHEN '02' THEN  PAF.META_02  " & _
                     "WHEN '03' THEN  PAF.META_03  " & _
                     "WHEN '04' THEN  PAF.META_04  " & _
                     "WHEN '05' THEN  PAF.META_05  " & _
                     "WHEN '06' THEN  PAF.META_06  " & _
                     "WHEN '07' THEN  PAF.META_07  " & _
                     "WHEN '08' THEN  PAF.META_08  " & _
                     "WHEN '09' THEN  PAF.META_09  " & _
                     "WHEN '10' THEN  PAF.META_10  " & _
                     "WHEN '11' THEN  PAF.META_11  " & _
                     "WHEN '12' THEN  PAF.META_12  " & _
                     "END  " & _
                     ") AS PORCENTAJE , " & _
                     " " + strCadAuxiEspecial + " AS ACUMULADO,  " & _
                      "(CASE WHEN FECHA_VISITA4 IS NOT NULL THEN NVL2(PFP.META_MENSUAL4, PFP.META_MENSUAL4, 0)  ELSE  " & _
                      "(CASE WHEN FECHA_VISITA3 IS NOT NULL THEN NVL2(PFP.META_MENSUAL3, PFP.META_MENSUAL3, 0)  ELSE  " & _
                      "(CASE WHEN FECHA_VISITA2 IS NOT NULL THEN NVL2(PFP.META_MENSUAL2, PFP.META_MENSUAL2, 0)  ELSE   " & _
                      "NVL2(PFP.META_MENSUAL1, PFP.META_MENSUAL1, 0)  END)END)END) AS EJECUTADO,  " & _
                       "ROUND(CASE WHEN  " + strCadAuxiEspecial + " =0 THEN 0 ELSE " & _
                      "(CASE WHEN FECHA_VISITA4 IS NOT NULL THEN NVL2(PFP.META_MENSUAL4, PFP.META_MENSUAL4, 0)  ELSE  " & _
                      "(CASE WHEN FECHA_VISITA3 IS NOT NULL THEN NVL2(PFP.META_MENSUAL3, PFP.META_MENSUAL3, 0)  ELSE  " & _
                      "(CASE WHEN FECHA_VISITA2 IS NOT NULL THEN NVL2(PFP.META_MENSUAL2, PFP.META_MENSUAL2, 0)  ELSE  " & _
                      "NVL2(PFP.META_MENSUAL1, PFP.META_MENSUAL1, 0)  END)END)END)/ " + strCadAuxiEspecial + " END,4) PORCENTAJE2 " & _
                      "FROM POA_FINANCIERA_HISTORICO PAF  " & _
                      "LEFT JOIN POA_HISTORICO P ON P.POA=PAF.POA  " & _
                      "LEFT JOIN ESPECIFICA_GASTO EG ON EG.ESPECIFICA_GASTO=PAF.ESPECIFICA_GASTO " & _
                      "LEFT JOIN POA_FINANCIERO_PERSONA PFP ON PFP.ID_POA_ACTIVIDAD_FINANCIERA=PAF.ID_POA_ACTIVIDAD_FINANCIERA " & _
                      "INNER JOIN POA_FINANCIERA_HISTORICO PAF ON PAF.ID_POA_ACTIVIDAD_FINANCIERA = PFP.ID_POA_ACTIVIDAD_FINANCIERA " & _
                      "INNER JOIN PLAN_TRABAJO_POA PTP ON PTP.ID_PLAN_TRABAJO_POA=PFP.ID_PLAN_TRABAJO_POA  " & _
                      "INNER JOIN PLAN_TRABAJO_PERSONA PTX ON PTX.ID_PLAN_TRABAJO_PERSONA=PTP.ID_PLAN_TRABAJO_PERSONA " & _
                      "INNER JOIN PERIODO PDO ON PDO.PERIODO=PTX.PERIODO  " & _
                      "WHERE PAF.POA= '" & strPOA & "' AND " & _
                            "PDO.PERIODO = '" & strPeriodo & "' AND " & _
                            "PFP.ESTADO='2' AND " & _
                            "P.VERSION = PAF.VERSION AND " & _
                            "P.VERSION = PFP.VERSION AND " & _
                            "P.VERSION = '" & strVersionPoaInforme & "' " & _
                      "ORDER BY PAF.ESPECIFICA_GASTO ASC "

            '"INNER JOIN POA_ACTIVIDAD_FINANCIERA PAF ON PAF.ID_POA_ACTIVIDAD_FINANCIERA = PFP.ID_POA_ACTIVIDAD_FINANCIERA " & _
            '"INNER JOIN PLAN_TRABAJO_POA PTP ON PTP.ID_PLAN_TRABAJO_POA=PFP.ID_PLAN_TRABAJO_POA " & _
            '"INNER JOIN PLAN_TRABAJO_PERSONA PTX ON PTX.ID_PLAN_TRABAJO_PERSONA=PTP.ID_PLAN_TRABAJO_PERSONA " & _
            '"INNER JOIN PERIODO PDO ON PDO.PERIODO=PTX.PERIODO " & _
            '"WHERE PAF.POA= '" & strPOA & "' AND  PDO.PERIODO='" & strPeriodo & "' AND PFP.ESTADO='2' ORDER BY PAF.ESPECIFICA_GASTO ASC "

            objdt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objdt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function CargarGrillaComentario() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT CM.ID_COMENTARIO_MONITOREO,CM.TIPO_COMENTARIO,CM.DESCRIPCION,CM.ID_INFORME_MENSUAL " + _
                      "FROM COMENTARIO_MONITOREO CM "


            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function CargarGrillaComentario(ByVal strcodim As String, ByVal strtipocom As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT CM.ID_COMENTARIO_MONITOREO,CM.TIPO_COMENTARIO,CM.DESCRIPCION,ID_INFORME_MENSUAL " + _
                      "FROM COMENTARIO_MONITOREO CM" + _
                      " where CM.ID_INFORME_MENSUAL='" + strcodim + "' AND CM.TIPO_COMENTARIO= '" & strtipocom & "' ORDER BY CM.ID_COMENTARIO_MONITOREO "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function CargarGrillaComentarioFn(ByVal strcodim As String, ByVal strtipocom As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT CM.ID_COMENTARIO_MONITOREO,CM.TIPO_COMENTARIO,CM.DESCRIPCION,ID_INFORME_MENSUAL " + _
                      "FROM COMENTARIO_MONITOREO CM" + _
                      " where CM.ID_INFORME_MENSUAL='" + strcodim + "' AND CM.TIPO_COMENTARIO= '" & strtipocom & "' ORDER BY CM.ID_COMENTARIO_MONITOREO "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub Insertarinforme(ByVal IDIM As String, ByVal POAIM As String, ByVal PDOIM As String, ByVal PERSO As String, ByVal EST As String, ByVal OBS As String, ByVal strVersionPoaVigente As String)
        Dim objData As AdministradorOracle = New AdministradorOracle

        Try
            objData.CreateInputOracle("IDIM", IDIM, 20)
            objData.CreateInputOracle("POAIM", POAIM, 20)
            objData.CreateInputOracle("PDOIM", PDOIM, 8)
            objData.CreateInputOracle("PERSO", PERSO, 8)
            objData.CreateInputOracle("EST", EST, 1)
            objData.CreateInputOracle("OBS", OBS, 4000)
            objData.CreateInputOracle("VERS_POA_VIGENTE", strVersionPoaVigente, 1)

            objData.ExecuteOracle_NonQuery("Insertarinforme", "Conexion.Oracle")

        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub Insertarcomentario(ByVal IDCM As String, ByVal TIPOCM As String, ByVal DESCR As String, ByVal IDIM As String)
        Dim objData As AdministradorOracle = New AdministradorOracle

        Try
            objData.CreateInputOracle("IDCM", IDCM, 20)
            objData.CreateInputOracle("TIPOCM", TIPOCM, 8)
            objData.CreateInputOracle("DESCR", DESCR, 4000)
            objData.CreateInputOracle("IDIM", IDIM, 8)

            objData.ExecuteOracle_NonQuery("Insertarcomentario", "Conexion.Oracle")


        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub Editacomentario(ByVal IDCM As String, ByVal IDIM As String, ByVal TIPOCM As String, ByVal DESCR As String)
        Dim objData As AdministradorOracle = New AdministradorOracle

        Try
            objData.CreateInputOracle("IDCM", IDCM, 20)
            objData.CreateInputOracle("IDIM", IDIM, 20)
            objData.CreateInputOracle("TIPOCM", TIPOCM, 20)
            objData.CreateInputOracle("DESCR", DESCR, 4000)


            objData.ExecuteOracle_NonQuery("Editacomentario", "Conexion.Oracle")


        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Public Function ListaMedidaFca(ByVal IDMM As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT PMM.ID_INFORME_MENSUAL, PMM.MEDIDA_FISICA " & _
                      "FROM POA_MENSUAL_MONITOREO PMM " & _
                      "WHERE PMM.ID_INFORME_MENSUAL= '" & IDMM & "' "

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaMedidaFin(ByVal IDMM As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT PMM.ID_INFORME_MENSUAL, PMM.MEDIDAD_FINANCIERA " & _
                      "FROM POA_MENSUAL_MONITOREO PMM " & _
                      "WHERE PMM.ID_INFORME_MENSUAL= '" & IDMM & "' "

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaFuenteFca(ByVal IDMM As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT PMM.ID_INFORME_MENSUAL, PMM.FUENTE_FISICA " & _
                      "FROM POA_MENSUAL_MONITOREO PMM " & _
                      "WHERE PMM.ID_INFORME_MENSUAL= '" & IDMM & "' "

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaFuenteFin(ByVal IDMM As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT PMM.ID_INFORME_MENSUAL, PMM.FUENTE_FINANCIERA " & _
                      "FROM POA_MENSUAL_MONITOREO PMM " & _
                      "WHERE PMM.ID_INFORME_MENSUAL= '" & IDMM & "' "

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub eliminarComentario(ByVal IDCM As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Try
            objData.CreateInputOracle("IDCM", IDCM, 20)

            objData.ExecuteOracle_NonQuery("eliminarcomentario", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub EditarInforme(ByVal IDIM As String, ByVal OBS As String)
        Dim objData As AdministradorOracle = New AdministradorOracle

        Try
            objData.CreateInputOracle("IDIM", IDIM, 20)
            objData.CreateInputOracle("OBS", OBS, 4000)

            objData.ExecuteOracle_NonQuery("EditarInforme", "Conexion.Oracle")

        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Public Function CargarObservaciones(ByVal strID As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT OBSERVACIONES FROM POA_MENSUAL_MONITOREO WHERE ID_INFORME_MENSUAL = '" + strID + "' "

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

End Class
    
